﻿using System.Data;
using Infragistics.Documents.Excel;

namespace Epicor.Pub
{
    /// <summary>Excel操作类</summary>
    public class PubExcel
    {
        Workbook workbook;
        /// <summary>实例化</summary>
        public PubExcel()
        {
            workbook = new Workbook();
        }
        /// <summary>加载Excel文件</summary>
        public bool Load(string sFileName)
        {
            try
            {
                workbook = Workbook.Load(sFileName);
                return true;
            }
            catch 
            {
                return false;
            }
        }
        /// <summary>Excel转DataTable</summary>
        public bool ToDataTable(ref DataTable dt,string sSheetName= "Sheet1", int headRow = 1)
        {
            try
            {
                Worksheet aWorksheet = workbook.Worksheets[sSheetName];
                foreach (var row in aWorksheet.Rows)
                {
                    if (row.Index >= headRow) {
                        DataRow dr = dt.NewRow();
                       var row0 =aWorksheet.Rows[0];
                        foreach (var cell in row.Cells) {
                            string colName = Convert26(cell.ColumnIndex+1);
                            if (headRow > 0) {
                                colName = row0.Cells[cell.ColumnIndex].Value.ToString();
                            }
                            if (dt.Columns.Contains(colName)) {
                                dr[colName] = cell.Value;
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }
            catch 
            {
                return false;
            }
            return true;
        }

        private string Convert26(int num) {
                var str = "";
                while (num > 0) {
                    var m = num % 26;
                    if (m == 0) m = 26;
                    str = ((char)(m + 64)).ToString() + str;
                    num = (num - m) / 26;
                }
                return str;
            }

        /// <summary>保存Excel文件</summary>
        public bool Save(string sFileName)
        {
            WorkbookFormat? format = Workbook.GetWorkbookFormat(sFileName);
            if (!format.HasValue)return false;
            try
            {
                workbook.SetCurrentFormat(format.Value);
                if (workbook.Worksheets.Count <= 0) workbook.Worksheets.Add("Sheet1");
                workbook.Save(sFileName);
                return true;
            }
            catch 
            {
                return false;
            }
        }


        /// <summary>创建Excel表格</summary>
        public bool CreateSheet(string sSheetName, DataTable dt, int headRow = 1) {

            try
            {
                Worksheet aWorksheet = workbook.Worksheets.Add(sSheetName);
                int beginRowIndex = 0;
                if (headRow == 1) beginRowIndex = 1;
                if (headRow >= 2) beginRowIndex = 2;
                for (int r=0;r< dt.Rows.Count;r++)
                {
                    for (int c = 0; c < dt.Columns.Count; c++)
                    {
                        if (r == 0 && beginRowIndex > 0) {
                            aWorksheet.Rows[0].Cells[c].Value = dt.Columns[c].ColumnName;
                            if (beginRowIndex > 1)aWorksheet.Rows[1].Cells[c].Value = dt.Columns[c].Caption;
                        }
                        aWorksheet.Rows[beginRowIndex+r].Cells[c].Value = dt.Rows[r][c];  
                    }
                    
                }
                return true;
            }
            catch 
            {
                return false;
            }
        }
        /// <summary>合并单元格,索引从0开始</summary>
        public void MergedCells(Worksheet sheet,int firstRow, int firstColumn, int lastRow, int lastColumn)
        {   
            sheet.MergedCellsRegions.Add(firstRow, firstColumn, lastRow, lastColumn);
        }


    }
}